
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import java.util.Date;
import java.util.Calendar;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class ChinaStockPriceSum {
	
	private static int CompanyNum = 0;
	private static Date SheetDates[] = {new Date("2007/7/1"), 
										new Date("2008/1/1"),
										new Date("2008/7/1"),
										new Date("2009/1/1"),
										new Date("2009/7/1"),
										new Date("2010/1/1"),
										new Date("2010/7/1"),
										new Date("2011/1/1"),
										new Date("2011/7/1"),
										new Date("2012/1/1"),
										new Date("2012/7/1")};

	public static void main(String[] args) {

		HSSFWorkbook workBookOut = new HSSFWorkbook();
		for(int i = 0;i< 11; i++)
		{
			workBookOut.createSheet();
		}

		
		System.out.println("before do");
		
		writeDates(workBookOut);

		File datas = new File("datas");
		File[] Files = datas.listFiles();
		for(int i=0; i<Files.length; i++)
			doWithOneFile(workBookOut, Files[i]);
			
		try {
			FileOutputStream fileOut = new FileOutputStream("ChinaStockPriceSum.xls");
			workBookOut.write(fileOut);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	
	public static void writeDates(HSSFWorkbook workBookOut) 
	{

		HSSFSheet sheetTask = null;
		
		Calendar SheetCalendar[]= new Calendar[11];
		for(int i=0; i< SheetCalendar.length; i++)
			SheetCalendar[i] = Calendar.getInstance();

		SheetCalendar[0].set(2007, Calendar.JULY, 1);
		SheetCalendar[1].set(2008, Calendar.JANUARY, 1);
		SheetCalendar[2].set(2008, Calendar.JULY, 1);
		SheetCalendar[3].set(2009, Calendar.JANUARY, 1);
		SheetCalendar[4].set(2009, Calendar.JULY, 1);
		SheetCalendar[5].set(2010, Calendar.JANUARY, 1);
		SheetCalendar[6].set(2010, Calendar.JULY, 1);
		SheetCalendar[7].set(2011, Calendar.JANUARY, 1);
		SheetCalendar[8].set(2011, Calendar.JULY, 1);
		SheetCalendar[9].set(2012, Calendar.JANUARY, 1);
		SheetCalendar[10].set(2012, Calendar.JULY, 1);
		
		int inputPosition = 0;
		for(int sheetNum = 0; sheetNum < SheetDates.length; sheetNum ++)
		{
			sheetTask = workBookOut.getSheetAt(sheetNum);
			HSSFRow rowTask = sheetTask.createRow(CompanyNum);
				
			int cell = 1;
			for (int j = 1; j < 256 ; j++) 
			{
				
				System.out.println(inputPosition);
				Calendar stockDay = Calendar.getInstance();
				stockDay.set(2007, Calendar.JANUARY, 1);
				stockDay.add(Calendar.DATE, inputPosition++);
				int dayOfWeek = stockDay.get(Calendar.DAY_OF_WEEK);
				if(dayOfWeek != Calendar.SATURDAY && dayOfWeek != Calendar.SUNDAY)
					rowTask.createCell(cell++).setCellValue(stockDay.get(Calendar.YEAR) + "/" 
														  + (stockDay.get(Calendar.MONTH)+1)+ "/"
														  + stockDay.get(Calendar.DAY_OF_MONTH));
				
				if(stockDay.after(SheetCalendar[sheetNum]))
					break;
			}
		}
		CompanyNum++;
	}

	public static void doWithOneFile(HSSFWorkbook workBookOut, File fileName) 
	{

		HSSFWorkbook workBookIn = null;
		HSSFSheet sheetTemp = null;
		HSSFSheet sheetTask = null;
		
		try {
			FileInputStream fileIn = new FileInputStream(fileName);
			POIFSFileSystem xlsFile = new POIFSFileSystem(fileIn);
			workBookIn = new HSSFWorkbook(xlsFile);
			fileIn.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	
		sheetTemp = workBookIn.getSheetAt(0);
		int inputPosition = 2;
		HSSFRow rowTemp = null;
		for(int sheetNum = 0; sheetNum < SheetDates.length; sheetNum ++)
		{
			sheetTask = workBookOut.getSheetAt(sheetNum);
			HSSFRow rowTask = sheetTask.createRow(CompanyNum);
			HSSFRow rowDate = sheetTask.getRow(0);
					
			rowTask.createCell(0).setCellValue(fileName.getName());
			
			int cellPos = 1;
			Date calendarDay = new Date(rowDate.getCell(cellPos).getStringCellValue());
			for (int j = 1; j <= sheetTemp.getLastRowNum() && j < 256 && inputPosition < sheetTemp.getLastRowNum(); j++) 
			{
				if(rowTemp == null)
					rowTemp = sheetTemp.getRow(inputPosition++);
						
				Date stockDay = new Date(rowTemp.getCell(0).getStringCellValue());
				System.out.println(rowTemp.getCell(0).getStringCellValue()
								+ " " + rowTemp.getCell(1).getNumericCellValue() 
								+ " " + rowTemp.getCell(2).getNumericCellValue() 
								+ " " + rowTemp.getCell(3).getNumericCellValue()
								+ " " + rowTemp.getCell(4).getNumericCellValue()
								+ " " + stockDay.toString());
				if(stockDay.after(SheetDates[sheetNum]))
					break;
				
				while(stockDay.compareTo(calendarDay) != 0)
					calendarDay = new Date(rowDate.getCell(++cellPos).getStringCellValue());

				rowTask.createCell(cellPos).setCellValue(rowTemp.getCell(4).getNumericCellValue());
				rowTemp = null;
			}
		}
		CompanyNum++;
	}
}
